	--level_e
drop table level_e;
create table level_e(
	level_no number primary key,
	level_point number not null,
	day_point number not null 
)


drop table Member_e;
	--member_e
create table Member_e(
	id varchar2(50) primary key,
	level_no number default 0,
	password varchar2(50) not null,
	ssn number unique,
	nick varchar2(50) unique,
	name varchar2(50) not null,
	address varchar2(50) not null,
	tel varchar2(50) not null,
	point number default 0,
	group_name varchar2(50),
	constraint fk_level_no foreign key(level_no) references level_e(level_no),
	constraint fk_group_name foreign key(group_name) references group_e(group_name)
)

ALTER TABLE Member_e DROP CONSTRAINT fk_level_no;

ALTER TABLE Member_e ADD CONSTRAINT fk_level_no FOREIGN KEY (level_no) 
   REFERENCES level_e(level_no) ON DELETE CASCADE

	--question_e
	
drop sequence question_seq;
create sequence question_seq;
drop table question_e;
create table question_e(
	question_no number primary key,
	level_no number not null,
	title varchar2(1000) not null,
	content varchar2(1000) not null,
	ex1 varchar2(1000) not null,
	ex2 varchar2(1000) not null,
	ex3 varchar2(1000) not null,
	ex4 varchar2(1000) not null,
	answer number not null,
	constraint fk_qlevel_no foreign key(level_no) references level_e(level_no)
)

	--test_e
drop sequence text_seq;
drop table test_e;
create sequence text_seq;
create table test_e(
	test_no number primary key,
	level_no number not null,
	id varchar2(50) not null,
	test_date date,
	test_confirm varchar2(50) not null,
	constraint fk_test_level_no foreign key(level_no) references level_e(level_no),
	constraint fk_test_id_no foreign key(id) references member_e(id)
)
	--mentoring_e
drop table mentoring_e;
create table mentoring_e (
	mento_id varchar2(50) not null,
	mentee_id varchar2(50) not null,
	mento_confirm number default 0,
	constraint fk_mentoring_mento foreign key(mento_id) references member_e(id),
    constraint fk_mentoring_mentee foreign key(mentee_id) references member_e(id),
    constraint pk_mentoring_mentoring primary key(mento_id,mentee_id)
)

	--note_e
drop table note_e;
drop sequence note_seq;
create sequence note_seq;

create table note_e(
   note_no number primary key,
   sender_id varchar2(50) not null,
   receiver_id varchar2(50) not null,
   renote_ref_no number default -1, -- -1이면 답글 아님, 0이 아니아니면 답글번호
   content varchar2(1000) not null,
   level_no number default -1, -- -1이면 일반쪽지, -1이 아니면 질문쪽지(등급) 
   note_ref_no number default -1, -- -1이면 일반쪽지, -1이 아니면 질문쪽지(원글번호)
   refOrder number default 1, -- 질문쪽지의 순서
   receiver_delete_confirm number default -1, -- 1이면 삭제
   sender_delete_confirm number default -1, -- 1이면 삭제
   send_date date,
   receive_date date, -- null이면 읽지 않음 notnull 읽음
   reject number default 0, -- 0이면 선택전 1이면 답장한 쪽지 -1이면 거절한 쪽지   
   constraint fk_sender_id foreign key(sender_id) references member_e(id),
   constraint fk_receiver_id foreign key(receiver_id) references member_e(id)
)

	--reply_e
create sequence reply_seq;
drop sequence reply_seq;
drop table reply_e;
create table reply_e(
	reply_no number primary key,
	id varchar2(50) not null,
	board_no number not null,
	content varchar2(1000) not null,
	reply_date date,
	reply_ref_no number,
	reply_restep number default 0,
	reply_relevel number default 0,
	
	constraint fk_reply_id foreign key(id) references member_e(id) ON DELETE cascade,
	constraint fk_reply_board_no foreign key(board_no) references board_e(board_no) ON DELETE cascade
	
)

insert into reply_e(reply_no,id,board_no,content,reply_date,reply_ref_no) values(reply_seq.nextval,'test',104,'루룰라라',sysdate,reply_seq.nextval)

select * from reply_e

delete from reply_e

SELECT content,id,to_char(reply_date,'YYYY.MM.DD') as reply_date
		
		FROM reply_e where board_no=144 order by reply_ref_no, reply_restep asc


drop table reply_e
	--board_e
	drop table board_e;
create sequence board_seq;
create table board_e(
	board_no number primary key,
	id varchar2(50) not null,
	category varchar2(50) not null,
	head varchar2(50) not null,
	title varchar2(50) not null,
	content varchar2(1000) not null,
	countview number default 0,
	write_date date,
	ref_no number not null,
	restep number default 0,
	relevel number default 0,
	orgfilename varchar2(500),
	newfilename varchar2(500),
	constraint fk_board_id foreign key(id) references member_e(id)
	
)

select * from board_e

ALTER TABLE board_e DROP CONSTRAINT fk_board_id;

ALTER TABLE board_e ADD CONSTRAINT fk_board_id FOREIGN KEY (id) 
   REFERENCES Member_e(id) ON DELETE CASCADE







